set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
----------------
-- Get user by ParentCode
-- k-phu
----------------
CREATE PROCEDURE [dbo].[UserLogin_SelectByParentCode]
@ParentCode VARCHAR(100)
as
Select * from UserLogin
 Where  ([ParentCode]= @ParentCode)
GO
----------------
-- Get user by SecurityCode
-- k-phu
----------------
CREATE PROCEDURE [dbo].[UserLogin_SelectBySecurityCode]
@SecurityCode VARCHAR(100)
as
Select * from UserLogin
 Where  ([SecurityCode]= @SecurityCode)

----------------
-- Calculate current sum device of user by SecurityCode
-- k-phu
----------------
CREATE PROCEDURE [dbo].[UserLogin_SumDeviceBySecurityCode]
@SecurityCode VARCHAR(100)
AS
SELECT SUM(NUMBEROFDEVICE) AS 'Total' 
  FROM USERLOGIN  
 WHERE PARENTCODE=@SecurityCode
 GROUP BY PARENTCODE


----------------
-- Delete user
-- k-phu
--@Type = 1 Real Delete
----------------
CREATE PROCEDURE [dbo].[UserLogin_DeleteUser]
@Username VARCHAR(100),
@Type CHAR(1)
AS
BEGIN
	DECLARE @result int;
	SET @result = 1;
	BEGIN TRANSACTION;
	BEGIN TRY
		-- Update device.userid
		UPDATE dv
		   SET USERID = NULL
		  FROM DEVICE dv
		  JOIN USERLOGIN  us
			ON dv.USERID = us.USERID
		 WHERE USERNAME=@Username;
		-- Delete user
		IF (@Type = '0')	
			-- Update parentcode
			UPDATE USERLOGIN
			SET PARENTCODE = NULL
			WHERE USERNAME = @Username;
		ELSE
			-- Real delte
			DELETE FROM USERLOGIN
			WHERE USERNAME = @Username;
	END TRY
	
	BEGIN CATCH
		SELECT ERROR_NUMBER() AS ErrorNumber;
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION;
	END CATCH;
	
	IF @@TRANCOUNT > 0
		COMMIT TRANSACTION;		
	ELSE
		SET @result = 0;

	SELECT @result as result;
END


----------------
-- Delete multip user
-- k-phu
--@Type = 1 Real Delete
----------------
CREATE PROCEDURE [dbo].[UserLogin_DeleteMultipUser]
@Username VARCHAR(100),
@Type CHAR(1)
AS
BEGIN
	DECLARE @result int;
	DECLARE @sql nvarchar(1000);
	SET @result = 1;
	BEGIN TRANSACTION;
	BEGIN TRY
		-- Update device.userid
		SET @sql = 'UPDATE dv
		   SET USERID = NULL
		  FROM DEVICE dv
		  JOIN USERLOGIN  us
			ON dv.USERID = us.USERID
		 WHERE USERNAME IN (' + @Username + ')';
		
		-- Execute sql
		EXECUTE sp_executesql @sql;

		-- Delete user
		IF (@Type = '0')	
			BEGIN
				-- Update parentcode
				SET @sql = 'UPDATE USERLOGIN
				SET PARENTCODE = NULL
				WHERE USERNAME IN (' + @Username + ')';

				-- Execute sql
				EXECUTE sp_executesql @sql;
			END
		ELSE
			BEGIN
				-- Real delte
				SET @sql = 'DELETE FROM USERLOGIN
				WHERE USERNAME IN(' + @Username+ ')';

				-- Execute sql
				EXECUTE sp_executesql @sql;
			END
	END TRY
	
	BEGIN CATCH
		SELECT ERROR_NUMBER() AS ErrorNumber;
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION;
	END CATCH;
	
	IF @@TRANCOUNT > 0
		COMMIT TRANSACTION;		
	ELSE
		SET @result = 0;

	SELECT @result as result;
END


----------------
-- Update password
-- k-phu
----------------
CREATE PROCEDURE [dbo].[UserLogin_UpdatePassword]
@SecurityCode VARCHAR(100),
@Password VARCHAR(100)
AS
UPDATE USERLOGIN 
   SET PASSWORD = @Password
 WHERE SECURITYCODE = @SecurityCode

----------------
-- Select all children user of admin
-- k-phu
----------------
CREATE PROCEDURE [dbo].[UserLogin_SelectAllByAdmin]
AS
SELECT *
  FROM USERLOGIN ul
 WHERE ul.USERGROUPID <> 3 
   AND ul.PARENTCODE IS NOT NULL
GO

execute [UserLogin_SelectAllByAdmin]